package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.zretc.dao.SingerDao;
import com.zretc.entity.Area;
import com.zretc.entity.Singer;
import com.zretc.entity.Song;
import com.zretc.entity.SongType;
import com.zretc.util.DBUtil;
import com.zretc.util.PageInfo;

/**
 * 歌手dao实现类
 * @author Administrator
 *
 */

public class SingerDaoImpl implements SingerDao {
	
	/**
	 * 根据歌手id获取歌手信息
	 * @author Administrator
	 * return PageInfo<Singer>
	 */
	@Override
	public PageInfo<Singer> findSingerBySingerId(Map<String, String> map) {
		PageInfo<Singer> pageInfo = null;
		List<Song> songList = new ArrayList<Song>();
		List<Singer> singerLsit = new ArrayList<>();
		Area area = null;
		ResultSet rs = null;
		Singer singer = new Singer();
		Song song = null;
		SongType songType = null;
		Integer singerId = Integer.parseInt(map.get("singerId"));
		Integer pageNum = Integer.valueOf(map.get("pageNum"));
		Integer pageSize = Integer.valueOf(map.get("pageSize"));
		rs = DBUtil.doQuery("select A.singer_id,A.singer_name,A.gender,A.singer_picture,B.area_id,B.area_name,C.song_id,C.song_name,"
				+ " C.song_time,C.click_count,C.song_text,C.song_status,D.type_id,D.type_name,C.publish_date "
				+ " from singer A left join area B on A.area_id = B.area_id left join song C "
				+ " on A.singer_id = C.singer_id left join songtype D on C.type_id = D.type_id "
				+ " where A.singer_id = ? limit ?,?", singerId,(pageNum-1)*pageSize,pageSize);
		try {
			while (rs.next()) {
				String singerName = rs.getString("singer_name");
				String gender = rs.getString("gender");
				String singerPicture = rs.getString("singer_picture");
				Integer areaId = rs.getInt("area_id");
				String areaName = rs.getString("area_name");
				Integer songId = rs.getInt("song_id");
				String songName = rs.getString("song_name");
				String songTime = rs.getString("song_time");
				Integer clickCount = rs.getInt("click_count");
				String songText = rs.getString("song_text");
				Integer songStatus = rs.getInt("song_status");
				Integer typeId = rs.getInt("type_id");
				String typeName = rs.getString("type_name");
				String publishDate = rs.getString("publish_date");
				area = new Area(areaId, areaName);
				songType = new SongType(typeId, typeName);
				song = new Song(songId, songName, songTime, clickCount, songStatus, songType, songText, publishDate);
				songList.add(song);
				singer.setArea(area);
				singer.setGender(gender);
				singer.setSingerName(singerName);
				singer.setSingerId(singerId);
				singer.setSingerPicture(singerPicture);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
			singer.setSongList(songList);
			singerLsit.add(singer);
			Integer total = 0;
			rs = DBUtil.doQuery("select count(*),A.singer_id,A.singer_name,A.gender,A.singer_picture,B.area_id,B.area_name,C.song_id,C.song_name,"
					+ " C.song_time,C.click_count,C.song_text,C.song_status,D.type_id,D.type_name,C.publish_date "
					+ " from singer A left join area B on A.area_id = B.area_id left join song C "
					+ " on A.singer_id = C.singer_id left join songtype D on C.type_id = D.type_id "
					+ " where A.singer_id = ?", singerId);
			
			try {
				while (rs.next()) {
					total = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			pageInfo = new PageInfo<>(singerLsit, pageNum, pageSize, total);
						
		return pageInfo;
	}

	@Override
	public PageInfo<Singer> findSingerByComdition(Map<String, String> map) {
		PageInfo<Singer> pageInfo = null;
		List<Singer> list = new ArrayList<>();
		Area area = null;
		ResultSet rs = null;
		Singer singer = null;
		String areaName = map.get("areaName");
		String gender = map.get("gender");
		Integer pageNum = Integer.valueOf(map.get("pageNum"));
		Integer pageSize = Integer.valueOf(map.get("pageSize"));
		
		rs = DBUtil.doQuery("select A.singer_id,A.singer_name,A.gender,A.singer_picture,B.area_id,B.area_name"
				+ " from singer A left join area B on A.area_id = B.area_id "
				+ " where B.area_name like ? and A.gender like ? limit ?,?", areaName,gender,(pageNum-1)*pageSize,pageSize);
		
		try {
			while (rs.next()) {
				Integer singerId = rs.getInt("singer_id");
				String singerName = rs.getString("singer_name");
				String gender1 = rs.getString("gender");
				String singerPicture = rs.getString("singer_picture");
				Integer areaId = rs.getInt("area_id");
				String areaName1 = rs.getString("area_name");
				area = new Area(areaId, areaName1);
				singer = new Singer(singerId, singerName, gender1, singerPicture, area);
				list.add(singer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
			Integer total = 0;
			rs = DBUtil.doQuery("select count(*),A.singer_id,A.singer_name,A.gender,A.singer_picture,B.area_id,B.area_name"
					+ " from singer A left join area B on A.area_id = B.area_id "
					+ " where B.area_name like ? and A.gender like ?", areaName,gender);
			
			try {
				while (rs.next()) {
					total = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			pageInfo = new PageInfo<>(list, pageNum, pageSize, total);
		
		return pageInfo;
	}

	@Override
	public List<String> findGender() {
		List<String> list = new ArrayList<>();
		ResultSet rs = DBUtil.doQuery("select gender from singer group by gender");
		
		try {
			while (rs.next()) {
				String string = rs.getString(0);
				list.add(string);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
}
